﻿CREATE PROCEDURE [maint].[RebuildIndexes]
@DbName [sysname], @Online BIT=0, @OutputDiagnostics BIT=0
AS
BEGIN

	DECLARE @RC INT;
	SET @RC = 0;

	DECLARE @Indexes TABLE([object_id] INT, [name] sysname);

	DECLARE @IndexQuery NVARCHAR(256);
	SET @IndexQuery = N'SELECT [object_id], [name] FROM [' + @DbName + '].sys.indexes WHERE (OBJECTPROPERTY(object_id,''IsMSShipped'') = 0) AND([name] IS NOT NULL)';

	INSERT INTO @Indexes
	EXEC(@IndexQuery);

	DECLARE IDX_CUR CURSOR FOR (
		SELECT * FROM @Indexes	
	)
	OPEN IDX_CUR
	DECLARE @object_id INT, @name sysname
	FETCH NEXT FROM IDX_CUR INTO @object_id, @name
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		DECLARE @rebuildStatement NVARCHAR(256);
		SET @rebuildStatement = N'ALTER INDEX '+ @name 
			+ N' ON [' + OBJECT_SCHEMA_NAME(@object_id) + N'].[' + OBJECT_NAME(@object_id)
			+ N'] REBUILD WITH(ONLINE=' + (CASE @Online WHEN 1 THEN N'ON' ELSE N'OFF' END) + N')';

		IF(@OutputDiagnostics = 1)
			PRINT N'Rebuilding index ' + @name + '...';

		BEGIN TRY
			EXEC(@rebuildStatement);
		END TRY
		BEGIN CATCH
			PRINT 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR)
				+ ', Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR)
				+ ', State ' + CAST(ERROR_STATE() AS NVARCHAR)
				+ ', Line ' + CAST(ERROR_LINE() AS NVARCHAR);
			PRINT ERROR_MESSAGE();
			
			SET @RC = ERROR_NUMBER();
		END CATCH
		
		FETCH NEXT FROM IDX_CUR INTO @object_id, @name;
	END
	CLOSE IDX_CUR;
	DEALLOCATE IDX_CUR;

	IF(@RC <> 0)
		RAISERROR('Failed to rebuild all indexes',16,1);

	RETURN(@RC);
END